package com.yckj.dao;

import com.yckj.Util.DbUtilHikari;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.hutool.crypto.SecureUtil;
import lombok.Data;

import javax.jws.soap.SOAPBinding;

/**
 * 对用户表的操作：注册用户、删除用户、修改用户和查询用户
 * @author lujingye
 * @version 1.0
 * @return 注册成功返回1，否则返回0
 */

public class UserDao {
    public List<User> selectAllUser(){
        //创建一个空集合，准备放用户对象
        List<User> userList = new ArrayList<User>();
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs =null;

        connection = DbUtilHikari.getConnection();
        String sql = "SELECT userid,username,userpsw FROM users";
        try {
            pstmt = connection.prepareStatement(sql);
            rs =pstmt.executeQuery();
            //rs里的数据转移到userList集合里
            while (rs.next()){
                //user当前是空的，一个“空包”
                User user = new User();
                user.setUserid(rs.getInt("userid"));
                user.setUsername(rs.getString("username"));
                user.setUserpsw(rs.getString("userpsw"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtilHikari.close(rs,pstmt,connection);
        }


        return userList;
    }



    /**
     *
     * @param userid
     * @param uname
     * @param upsw
     * @return修改成功返回，否则返回0
     */
    public int updateUser(int userid,String uname,String upsw){
        int flag = 0;
        Connection connection = null;
        PreparedStatement pstmt = null;
        connection = DbUtilHikari.getConnection();
        String sql = "UPDATE users SET username=?,userpsw=? WHERE userid=?";
        try {
            pstmt =connection.prepareStatement(sql);
            pstmt.setString(1,uname);
            pstmt.setString(2,SecureUtil.md5(upsw));
            pstmt.setInt(3,userid);
            flag =pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbUtilHikari.close(pstmt,connection);
        }


        return flag;

    }

    public int deletUser(int userid){
        int flag = 0;
        //声明一些变量
        Connection connection =null;
        PreparedStatement pstmt = null;
        //获取数据库连接
        connection = DbUtilHikari.getConnection();
        //定义并准备SQL语句
        String sql ="DELETE FROM users WHERE userid=?";
        try {
            pstmt = connection.prepareStatement(sql);
            //给？赋值
            pstmt.setInt(1,userid);
            //执行SQL语句
            flag = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源（释放资源）
            DbUtilHikari.close(pstmt,connection);
        }
        return  flag;
    }


    public boolean login(String uname,String upsw){
        /**
         * 按用户名和密码登录
         * @param uname 用户名
         * @param upsw 密码
         * @return 如果登录成功，返回true;否则返回false
         */
        //flag是登录成功与否的标志，如果true表示登录成功
        boolean flag =false;
        //commection表示数据库连接
        Connection connection = null;
        //执行SQL语句
        PreparedStatement pstmt = null;
        //rs代表结果集，从数据库里查到的结果放在rs里
        ResultSet rs = null;

        //获得数据库连接,从连接池获取,不是直接从数据库打开连接
        //用连接池能提高数据库访问性能，特别是在大并发场景下
        connection = DbUtilHikari.getConnection();
        //定义并准备SQL语句,登录是查询语句select
        String sql = "SELECT * FROM users WHERE username=? AND userpsw=?";
        try {
            pstmt = connection.prepareStatement(sql);
            //给？赋值
            pstmt.setString(1,uname);
            pstmt.setString(2,SecureUtil.md5(upsw));
            //下达执行指令，执行SQL语句
            rs = pstmt.executeQuery();
            //判断rs结果集里有没有数据，next()判断rs里有数据吗？
            //如果next()返回true,说明rs结果集里有数据
            //如果next（）返回false，说明rs结果集里没有数据
            if (rs.next()){
                flag=true;
            }else{
                flag=false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            DbUtilHikari.close(rs,pstmt,connection);
        }
        return flag;
    }

    public int insertUser(String uname,String upsw){
        //1，声明一些变量备用
        //代表数据库链接
        Connection connection;
        //预编译对象，用来执行SQL语句
        PreparedStatement preparedStatement = null;
        int flag = 0;

        //2,。获取连接
        connection = DbUtilHikari.getConnection();
        //3.定义并准备SQL语句
        String sql = "INSEOR INTO users(username,userpsw) VALUES(?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            //4.给？赋值
            preparedStatement.setString(1,uname);
            preparedStatement.setString(2,SecureUtil.md5(upsw));
            //5.执行SQL语句
            flag = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库资源
            DbUtilHikari.close(preparedStatement,connection);
        }
        return flag;
    }
}
